package server;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.Queue;

import ocsf.server.ConnectionToClient;
import messages.*;
import entities.*;

/**
 * 
 */
public class ServerHandler {
	
	/**
	 * the connector to DB
	 */
	private DBConnector dbc;
	
	/**
	 * Map holding all currently connected users
	 */
	private Map<Long,ConnectionToClient> connected_users;
	
	private Queue<Email> emails_list;
	
	public ServerHandler(DBConnector dbc){
		this.dbc = dbc;
		this.connected_users = new HashMap<Long,ConnectionToClient>();
		this.emails_list = (new LinkedList<Email>());
	}
	
	public Map<Long, ConnectionToClient> getConnectedUsers(){
		return this.connected_users;
	}
	
	/**
	 * Perform ICM-Project-GRP14 any Action received from client's Message
	 */
	public void readMsg(Object message, ConnectionToClient client){
		Message msg = (Message)message;
		Message returnMsg = null;
		
		// System.out.println("Recieved Message: " + msg.getMsgType());
		
		/**
		 * Each message has its unique action according to the message's type 
		 */
		if(msg.getMsgType().equals(MsgType.LOGIN_REQUEST)) {
			LoginMsg loginMsg = (LoginMsg)msg.getObject();
			returnMsg = loginQuery(loginMsg, client);
		}
		else if(msg.getMsgType().equals(MsgType.LOGOUT_REQUET)) {
			Long userID = (Long) msg.getObject();
			logoutRequest(userID);
		}
		else if(msg.getMsgType().equals(MsgType.INFO_SYSTEMS_REQUEST)) {
			returnMsg = systemQuery();
		}
		else if(msg.getMsgType().equals(MsgType.NEW_TICKET)) {
			// In case of new ticket - we need to store it and send the user the ticket ID number
			returnMsg = newTicketQuery((Ticket)msg.getObject());			
		}
		else if(msg.getMsgType().equals(MsgType.TICKET_DETAILS_REQUEST)) {
			returnMsg = ticketDetailsQuery(msg, 1);
		}
		else if(msg.getMsgType().equals(MsgType.TICKET_LIST_ALL_REQUEST)) {
			returnMsg = ticketListAllQuery();
		}
		else if(msg.getMsgType().equals(MsgType.TICKET_LIST_STAGE_REQUEST)) {
			returnMsg = ticketListStageQuery(msg);
		}
		else if(msg.getMsgType().equals(MsgType.TICKET_LIST_STATUS_REQUEST)) {
			returnMsg = ticketListStatusQuery(msg);
		}
		else if(msg.getMsgType().equals(MsgType.LIST_ALL_USERS_REQUEST)) {
			returnMsg = listAllUsers();
		}
		else if(msg.getMsgType().equals(MsgType.LIST_ALL_IE_USERS_REQUEST)) {
			returnMsg = listAllIEUsers();
		}
		else if(msg.getMsgType().equals(MsgType.ASSIGN_INSPECTOR_TO_TICKET_REQUEST)) {
			returnMsg = assignInspectorQuery(msg);
		}
		else if(msg.getMsgType().equals(MsgType.ASSIGN_RESPONSIBLES_TO_TICKET_REQUEST)) {
			returnMsg = assignResponsiblesQuery(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SET_EVALUATION_DURATION_REQUEST)) {
			returnMsg = setEvaluationDuration(msg);
		}
		else if(msg.getMsgType().equals(MsgType.APPROVE_EVALUATION_DURATION_REQUEST)) {
			returnMsg = approveEvaluationDuration(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SET_EVALUATION_REPORT_REQUEST)){
			returnMsg = setEvaluationReport(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SET_REVIEW_DECISION_REPORT_REQUEST)){
			returnMsg = setRevewDecisionReport(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SET_EXECUTION_DURATION_REQUEST)){
			returnMsg = setExecutionDuration(msg);
		}
		else if(msg.getMsgType().equals(MsgType.APPROVE_EXECUTION_DURATION_REQUEST)){
			returnMsg = approveExecutionDuration(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SET_EXECUTION_REPORT_REQUEST)){
			returnMsg = setExecutionReport(msg);
		}
		else if(msg.getMsgType().equals(MsgType.ASSIGN_TESTER_TO_TICKET_REQUEST)){
			returnMsg = assignTesterToTicket(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SET_VERIFICATION_REPORT_REQUEST)){
			returnMsg = setVerificationReport(msg);
		}
		else if(msg.getMsgType().equals(MsgType.INITIATOR_APPROVE_CLOSE_REQUEST)){
			returnMsg = initiatorApproveClose(msg);
		}
		else if(msg.getMsgType().equals(MsgType.INSPECTOR_APPROVE_CLOSE_REQUEST)){
			returnMsg = inspectorApproveClose(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SUSPEND_TICKET_REQUEST)){
			returnMsg = suspendTicket(msg);
		}
		else if(msg.getMsgType().equals(MsgType.RESUME_TICKET_REQUEST)){
			returnMsg = resumeTicket(msg);
		}
		else if(msg.getMsgType().equals(MsgType.SET_TIME_EXTENSION_REQUEST_REQUEST)){
			returnMsg = setTimeExtensionRequest(msg);
		}
		else if(msg.getMsgType().equals(MsgType.APPROVE_TIME_EXTENSION_REQUEST_REQUEST)){
			returnMsg = approveTimeExtension(msg);
		}
		else if(msg.getMsgType().equals(MsgType.LIST_TIME_EXTENSTIONS_REQUESTS_REQUEST)){
			returnMsg = listTimeExtensionRequests(msg);
		}
		else if(msg.getMsgType().equals(MsgType.REPLACE_COMMITTEE_CHAIRMAN_REQUEST)){
			returnMsg = replaceCommitteeChairman(msg);
		}
		else if(msg.getMsgType().equals(MsgType.REPLACE_COMMITTEE_MEMBER_REQUEST)){
			returnMsg = replaceCommitteeMember(msg);
		}
		else if(msg.getMsgType().equals(MsgType.REPLACE_INSPECTOR_TO_TICKET_REQUEST)){
			returnMsg = replaceInspectorToTicket(msg);
		}
		else if(msg.getMsgType().equals(MsgType.TICKET_TRACK_REQUEST)){
			returnMsg = ticketDetailsQuery(msg, 2);
		}
		else if(msg.getMsgType().equals(MsgType.LIST_ALL_COMMITTEE_MEMBERS_REQUEST)){
			returnMsg = listAllCommitteeMembers();
		}
		
		
		/**
		 * After creating the returned message, it's being delivered back to client
		 */
		try {
			client.sendToClient(returnMsg);
			// System.out.println("Sent Message: " + returnMsg.getMsgType());
		} catch (IOException e) {
			System.out.println("Error sending to client: " + e.getMessage());
		}
	}

	/**
	 * Handles user login request
	 * @param loginMsg
	 * @param client
	 * @return
	 */
	private Message loginQuery(LoginMsg loginMsg, ConnectionToClient client) {
		Message returnedMsg;
		
		System.out.println("** Loging-In With: " + loginMsg.getUsername());
		
		// User is already connected to server
		if( connected_users.containsKey(loginMsg.getUsername()) ){
			returnedMsg = new Message(MsgType.LOGIN_CONNECTED_ERR, null, null);
			return returnedMsg;
		}
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL login(" + loginMsg.getUsername() + ",\"" + loginMsg.getPassword() + "\");");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			// User and Password correct
			if(rs.next()) {
				User newUser = new User(loginMsg.getUsername(), rs.getString("u_f_name"), rs.getString("u_l_name"), rs.getString("u_email"), rs.getString("u_phone"), rs.getString("permissions"));
				connected_users.put(loginMsg.getUsername(), client);
				returnedMsg = new Message(MsgType.LOGIN_OK, ObjectType.USER, newUser);
				// emails_list.add(new Email("icm.grp14@gmail.com", "0547458506", "The user " + rs.getString("u_f_name") + " " + rs.getString("u_l_name") +" has just logged in"));
			}
			else{ // User or Password incorrect
				returnedMsg = new Message(MsgType.LOGIN_USER_PSW_ERR, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.LOGIN_CONNETION_ERR, null, null);
		}		
		dbc.closeConn(conn);
		// System.out.println(returnedMsg.getMsgType().toString());
		return returnedMsg;
	}
	
	/**
	 * Handles user logout request
	 * @param userID Long
	 */
	private void logoutRequest(Long userID) {
		connected_users.remove(userID);
		System.out.println("ServerHandler.logoutRequest() - " + userID + " has disconnected.");
	}
	
	/**
	 * Sending a request to the DB to list all available Information Systems and return them to client
	 * @return
	 */
	private Message systemQuery() {
		Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL getAllSystems();");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		ArrayList<InfoSystem> infoSystems = new ArrayList<InfoSystem>();
		try {
			while(rs.next()) {
				InfoSystem is = new InfoSystem(rs.getInt("s_id"), rs.getString("s_name"));
				infoSystems.add(is);
			}
			if(infoSystems.isEmpty()) {
				returnedMsg = new Message(MsgType.INFO_SYSTEMS_RESPONSE_ERR, null, null);
			}
			else {
				returnedMsg = new Message(MsgType.INFO_SYSTEMS_RESPONSE, ObjectType.INFO_SYSTEM, new InfoSystemsMsg(infoSystems));
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.INFO_SYSTEMS_RESPONSE_ERR, null, null);
		}
		
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Handles user's add new ticket request
	 * @param ticket
	 * @return
	 */
	private Message newTicketQuery(Ticket ticket) {
		Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL addNewTicket(" + ticket.getTicketInitiator().getId() + "," + ticket.getInfoSystem().getS_id() + ",\"" + 
								ticket.getGeneralInfo().getExistingSituationDescription() + "\",\"" + ticket.getGeneralInfo().getRequestedChangedDescription() + "\",\"" + 
								ticket.getGeneralInfo().getReasonForRequest() + "\",\"" + ticket.getGeneralInfo().getComments() + "\");");
		ResultSet rs = dbc.runQuery(qry, conn);
		try {
			if(rs.next()) {
				returnedMsg = new Message(MsgType.TICKET_RESPONSE, ObjectType.LONG, rs.getLong(1));
			}
			else {
				returnedMsg = new Message(MsgType.TICKET_ERR, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.TICKET_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Retrieving the details of a selected ticket from the DB by the ticket's id 
	 * @param msg, flag --> flag = 1 -> ticket details request, flag = 2 -> ticket track request
	 * @return
	 */
	private Message ticketDetailsQuery(Message msg, int flag) {
		Message returnedMsg;		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL showTicketDetails(" + (Long)msg.getObject() + ");");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			if(rs.next()) { 
				Ticket ticket = new Ticket();
				/****************************************
				 * populating ticket's general details
				 ***************************************/
				User initiator = new User();
				initiator.setId(rs.getLong("initiator_id"));
				initiator.setFirstName(rs.getString("initiator_f_name"));
				initiator.setLastName(rs.getString("initiator_l_name"));
				initiator.setEmail(rs.getString("initiator_email"));
				initiator.setPhone(rs.getString("initiator_phone"));
				ticket.setUser(initiator);
				User inspector = new User();
				inspector.setId(rs.getLong("inspector_id"));
				inspector.setFirstName(rs.getString("inspector_f_name"));
				inspector.setLastName(rs.getString("inspector_l_name"));
				ticket.setInspector(inspector);
				ticket.setTicket_id(rs.getLong("ticket_id"));
				GeneralInformationStage generalInfo = new GeneralInformationStage();
				generalInfo.setExistingSituationDescription(rs.getString("t_cur_status_desc"));
				generalInfo.setRequestedChangedDescription(rs.getString("t_change_desc"));
				generalInfo.setReasonForRequest(rs.getString("t_benefit_desc"));
				generalInfo.setComments(rs.getString("t_comments"));
				generalInfo.setInitDate(rs.getString("t_date"));
				generalInfo.setInfoSystemName(rs.getString("system_name"));
				ticket.setGeneralInfo(generalInfo);
				ticket.setStatus(rs.getString("status_name"));
				ticket.setState(rs.getString("stage_name"));
				ticket.setInfoSystem(new InfoSystem(rs.getString("system_name")));
				/***********************************************
				 * populating evaluation report stage details
				 **********************************************/
				EvaluationReportStage ers = new EvaluationReportStage();
				ers.setTicketId(rs.getLong("ticket_id"));
				User evaluator = new User();
				evaluator.setId(rs.getLong("evaluator_id"));
				evaluator.setFirstName(rs.getString("evaluator_f_name"));
				evaluator.setLastName(rs.getString("evaluator_l_name"));
				ers.setEvaluator(evaluator);
				ers.setApproveDate(rs.getString("tme_aprv_date"));
				ers.setDurationDate(rs.getString("tme_duration_date"));
				ers.setDurationApproveDate(rs.getString("tme_dur_aprv_date"));
				ers.setChangeDescreption(rs.getString("tme_change_desc"));
				ers.setResultDescription(rs.getString("tme_result_desc"));
				ers.setLimitationsDescription(rs.getString("tme_limitations_desc"));
				ers.setRisksDescriptions(rs.getString("tme_risks_desc"));
				ers.setTotalDurationDate(rs.getString("tme_tot_duration_date"));
				ers.setCompleteDate(rs.getString("tme_complete_date"));
				ers.setDurationDays( rs.getInt("tme_duration_days") );
				ticket.setEvaluationReportStage(ers);
				/*************************************************
				 * populating review and decision stage details
				 ************************************************/
				ReviewDecisionStage rds = new ReviewDecisionStage();
				rds.setTicketId(rs.getLong("ticket_id"));
				rds.setDurationDate(rs.getString("trd_duration_date"));
				rds.setDecision(rs.getString("trd_decision"));
				rds.setCompleteDate(rs.getString("trd_complete_date"));
				ticket.setReviewDecisionStage(rds);
				/***************************************
				 * populating execution stage details
				 **************************************/
				ExecutionStage es = new ExecutionStage();
				es.setTicketId( rs.getLong("ticket_id") );
				User leader = new User();
				leader.setId(rs.getLong("leader_id"));
				leader.setFirstName(rs.getString("leader_f_name"));
				leader.setLastName(rs.getString("leader_l_name"));
				es.setLeader(leader);
				es.setDurationDate(rs.getString("te_duration_date"));
				es.setDurationApproveDate(rs.getString("te_dur_aprv_date"));
				es.setCompleteDate(rs.getString("te_complete_date"));
				es.setDurationDays(rs.getInt("te_duration_days"));
				ticket.setExecutionStage(es);
				/******************************************
				 * populating verification stage details
				 *****************************************/
				VerificationStage vs = new VerificationStage();
				vs.setTicketId(rs.getLong("ticket_id"));
				User tester = new User();
				tester.setId(rs.getLong("tester_id"));
				tester.setFirstName(rs.getString("tester_f_name"));
				tester.setLastName(rs.getString("tester_l_name"));
				vs.setTester(tester);
				vs.setDurationDate(rs.getString("tv_duration_date"));
				vs.setResult(rs.getString("tv_result"));
				vs.setResultDescription(rs.getString("tv_result_desc"));
				vs.setCompleteDate(rs.getString("tv_complete_date"));
				ticket.setVerificationStage(vs);
				/************************************
				 * populating closer stage details
				 ***********************************/
				ClosureStage cs = new ClosureStage();
				cs.setTicketId(rs.getLong("ticket_id"));
				cs.setInspectorApproveDate(rs.getString("tc_inspector_aprv_date"));
				cs.setInitiatorApproveDate(rs.getString("tc_initiator_aprv_date"));
				cs.setCompleteDate(rs.getString("tc_complete_date"));
				ticket.setClosureStage(cs);
				if(flag == 1)
					returnedMsg = new Message(MsgType.TICKET_DETAILS_RESPONSE, ObjectType.TICKET, ticket);
				else
					returnedMsg = new Message(MsgType.TICKET_TRACK_RESPONSE, ObjectType.TICKET, ticket);
			}
			else {
				if(flag == 1)
					returnedMsg = new Message(MsgType.TICKET_DETAILS_ERR, null, null);
				else
					returnedMsg = new Message(MsgType.TICKET_TRACK_ERR, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			if(flag == 1)
				returnedMsg = new Message(MsgType.TICKET_DETAILS_ERR, null, null);
			else
				returnedMsg = new Message(MsgType.TICKET_TRACK_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * retrieving a list of the all tickets in the DB
	 * @param msg
	 * @return
	 */
	public Message ticketListAllQuery() {
		Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL listTickets(null, null, null, null, null, null, null);");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			ArrayList<Ticket> tickets = new ArrayList<Ticket>();
			while(rs.next()) {
				Ticket t = new Ticket();
				t.setTicket_id(rs.getLong("ticket_id"));
				User initiator = new User();
				initiator.setId(rs.getLong("initiator_id"));
				initiator.setFirstName(rs.getString("initiator_f_name"));
				initiator.setLastName(rs.getString("initiator_l_name"));
				t.setUser(initiator);
				InfoSystem is = new InfoSystem(rs.getString("system_name"));
				t.setInfoSystem(is);
				GeneralInformationStage generalInfo = new GeneralInformationStage();
				generalInfo.setRequestedChangedDescription(rs.getString("t_change_desc"));
				generalInfo.setInitDate(rs.getString("t_date"));
				t.setGeneralInfo(generalInfo);
				t.setStatus(rs.getString("status_name"));
				t.setState(rs.getString("stage_name"));
				tickets.add(t);
			}
			if(tickets.size() > 0) {
				returnedMsg = new Message(MsgType.TICKET_LIST_RESPONSE, ObjectType.TICKET_ARRAY_LIST, tickets);
			}
			else {
				returnedMsg = new Message(MsgType.TICKET_LIST_EMPTY, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.TICKET_LIST_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * retrieving a list ticket of a selected stage
	 * @param msg
	 * @return
	 */
	public Message ticketListStageQuery(Message msg) {
		Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry;
		String userIndicator = ((TicketListMsg)msg.getObject()).getUserIndicator();
		switch(userIndicator) {
		case "initiator":
			qry = new Query(true, "CALL listTickets(" + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL," + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL, NULL, NULL, NULL);");
			break;
		case "inspector":
			qry = new Query(true, "CALL listTickets(" + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL, NULL, NULL);");
			break;
		case "evaluator":
			qry = new Query(true, "CALL listTickets(" + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL, NULL);");
			break;
		case "leader":
			qry = new Query(true, "CALL listTickets(" + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, NULL, NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL);");
			break;
		case "tester":
			qry = new Query(true, "CALL listTickets(" + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, NULL, NULL, NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ");");
			break;
		default:
			qry = new Query(true, "CALL listTickets(" + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, NULL, NULL, NULL, NULL);");
		}
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			ArrayList<Ticket> tickets = new ArrayList<Ticket>();
			while(rs.next()) {
				Ticket t = new Ticket();
				t.setTicket_id(rs.getLong("ticket_id"));
				User initiator = new User();
				initiator.setId(rs.getLong("initiator_id"));
				initiator.setFirstName(rs.getString("initiator_f_name"));
				initiator.setLastName(rs.getString("initiator_l_name"));
				t.setUser(initiator);
				InfoSystem is = new InfoSystem(rs.getString("system_name"));
				t.setInfoSystem(is);
				GeneralInformationStage generalInfo = new GeneralInformationStage();
				generalInfo.setRequestedChangedDescription(rs.getString("t_change_desc"));
				generalInfo.setInitDate(rs.getString("t_date"));
				t.setGeneralInfo(generalInfo);
				t.setStatus(rs.getString("status_name"));
				t.setState(rs.getString("stage_name"));
				tickets.add(t);
			}
			if(tickets.size() > 0) {
				returnedMsg = new Message(MsgType.TICKET_LIST_RESPONSE, ObjectType.TICKET_ARRAY_LIST, tickets);
			}
			else {
				returnedMsg = new Message(MsgType.TICKET_LIST_EMPTY, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.TICKET_LIST_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * retrieving a list ticket of a selected status
	 * @param msg
	 * @return
	 */
	public Message ticketListStatusQuery(Message msg) {
		Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry;
		String userIndicator = ((TicketListMsg)msg.getObject()).getUserIndicator();
		switch(userIndicator) {
		case "initiator":
			qry = new Query(true, "CALL listTickets(NULL, " + ((TicketListMsg)msg.getObject()).getStatusName() + ", " + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL, NULL, NULL, NULL);");
			break;
		case "inspector":
			qry = new Query(true, "CALL listTickets(NULL, " + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL, NULL, NULL);");
			break;
		case "evaluator":
			qry = new Query(true, "CALL listTickets(NULL, " + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL, NULL);");
			break;
		case "leader":
			qry = new Query(true, "CALL listTickets(NULL, " + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ", NULL);");
			break;
		case "tester":
			qry = new Query(true, "CALL listTickets(NULL, " + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, NULL, NULL, " + ((TicketListMsg)msg.getObject()).getUserId() + ");");
			break;
		default:
			qry = new Query(true, "CALL listTickets(NULL, " + ((TicketListMsg)msg.getObject()).getStatusName() + ", NULL, NULL, NULL, NULL, NULL);");
		}
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			ArrayList<Ticket> tickets = new ArrayList<Ticket>();
			while(rs.next()) {
				Ticket t = new Ticket();
				t.setTicket_id(rs.getLong("ticket_id"));
				User initiator = new User();
				initiator.setId(rs.getLong("initiator_id"));
				initiator.setFirstName(rs.getString("initiator_f_name"));
				initiator.setLastName(rs.getString("initiator_l_name"));
				t.setUser(initiator);
				InfoSystem is = new InfoSystem(rs.getString("system_name"));
				t.setInfoSystem(is);
				GeneralInformationStage generalInfo = new GeneralInformationStage();
				generalInfo.setRequestedChangedDescription(rs.getString("t_change_desc"));
				generalInfo.setInitDate(rs.getString("t_date"));
				t.setGeneralInfo(generalInfo);
				t.setStatus(rs.getString("status_name"));
				t.setState(rs.getString("stage_name"));
				tickets.add(t);
			}
			if(tickets.size() > 0) {
				returnedMsg = new Message(MsgType.TICKET_LIST_RESPONSE, ObjectType.TICKET_ARRAY_LIST, tickets);
			}
			else {
				returnedMsg = new Message(MsgType.TICKET_LIST_EMPTY, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.TICKET_LIST_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Retrieving a list of all users in system 
	 * @return
	 */
	public Message listAllUsers(){
	Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL listAllUsers();");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			ArrayList<User> users = new ArrayList<User>();
			while(rs.next()) {
				User u = new User(rs.getLong("u_id"), rs.getString("u_f_name"), rs.getString("u_l_name"), rs.getString("u_email"), rs.getString("u_phone"), rs.getString("permissions"));
				users.add(u);
			}
			if(users.size() > 0) {
				returnedMsg = new Message(MsgType.LIST_ALL_USERS_RESPONSE, ObjectType.USER_ARRAY_LIST, users);
			}
			else {
				returnedMsg = new Message(MsgType.LIST_ALL_USERS_EMPTY, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.LIST_ALL_USERS_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Retrieving a list of all IE users in system 
	 * @return
	 */
	public Message listAllIEUsers(){
	Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL listAllIEUsers();");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			ArrayList<User> users = new ArrayList<User>();
			while(rs.next()) {
				User u = new User(rs.getLong("u_id"), rs.getString("u_f_name"), rs.getString("u_l_name"), rs.getString("u_email"), rs.getString("u_phone"));
				users.add(u);
			}
			if(users.size() > 0) {
				returnedMsg = new Message(MsgType.LIST_ALL_IE_USERS_RESPONSE, ObjectType.USER_ARRAY_LIST, users);
			}
			else {
				returnedMsg = new Message(MsgType.LIST_ALL_IE_USERS_EMPTY, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.LIST_ALL_IE_USERS_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to assign an inspector to a ticket
	 * @param msg
	 * @return
	 */
	public Message assignInspectorQuery(Message msg) {
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = inspector_id
		// object[1] = ticket_id
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL assignInspectorToTicket(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.ASSIGN_INSPECTOR_TO_TICKET_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.ASSIGN_INSPECTOR_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to assign responsible (evaluator and executor) to a ticket
	 * @param msg
	 * @return
	 */
	public Message assignResponsiblesQuery(Message msg) {
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		// object[1] = evaluator_id or null 
		// object[2] = executor_id
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL assignResponsiblesToTicket(" + queryParams.get(0) + ", " + queryParams.get(1) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.ASSIGN_RESPONSIBLES_TO_TICKET_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.ASSIGN_RESPONSIBLES_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}

	/**
	 * Sending a request to the DB to set the evaluation duration
	 * @param msg
	 * @return
	 */
	public Message setEvaluationDuration(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		// object[1] = days
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL setEvaluationDuration(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SET_EVALUATION_DURATION_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.SET_EVALUATION_DURATION_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to Approve Evaluation Duration
	 * @param msg
	 * @return
	 */
	public Message approveEvaluationDuration(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		// object[1] = days 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL approveEvaluationDuration(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.APPROVE_EVALUATION_DURATION_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.ASSIGN_RESPONSIBLES_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to insert the evaluation report
	 * @param msg
	 * @return
	 */
	public Message setEvaluationReport(Message msg){
		Message returnedMsg;
		EvaluationReportStage ers = (EvaluationReportStage)msg.getObject();
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL setEvaluationReport(" + ers.getTicketId() + ", \"" + ers.getChangeDescreption() + "\", \"" + ers.getResultDescription() + "\", \"" + ers.getRisksDescriptions() + "\", \"" + ers.getTotalDurationDate() + "\");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SET_EVALUATION_REPORT_RESPONSE, null, null);

		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to insert the "Review and Decision" Report
	 * @param msg
	 * @return
	 */
	public Message setRevewDecisionReport(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		// object[1] = decision 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL setRevewDecisionReport(" + queryParams.get(0) + ", \"" + queryParams.get(1) + "\");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SET_REVIEW_DECISION_REPORT_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.SET_REVIEW_DECISION_REPORT_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to insert the Execution Duration
	 * @param msg
	 * @return
	 */
	public Message setExecutionDuration(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		// object[1] = days 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL setExecutionDuration(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SET_EXECUTION_DURATION_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.SET_EXECUTION_DURATION_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to approve the Execution Duration
	 * @param msg
	 * @return
	 */
	public Message approveExecutionDuration(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		// object[1] = days OR null
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL approveExecutionDuration(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.APPROVE_EXECUTION_DURATION_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.APPROVE_EXECUTION_DURATION_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to insert the Execution Report
	 * @param msg
	 * @return
	 */
	public Message setExecutionReport(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL setExecutionReport(" + queryParams.get(0) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SET_EXECUTION_REPORT_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.SET_EXECUTION_REPORT_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to Assign Tester to Ticket
	 * @param msg
	 * @return
	 */
	public Message assignTesterToTicket(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();

		// object[0] = tester_id
		// object[1] = ticket_id 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL assignTesterToTicket(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.ASSIGN_TESTER_TO_TICKET_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.ASSIGN_TESTER_TO_TICKET_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to insert the Verification Report
	 * @param msg
	 * @return
	 */
	public Message setVerificationReport(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();

		// object[0] = ticket_id
		// object[1] = decision
		// object[2] = decision description
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL setVerificationReport(" + queryParams.get(0) + ", \"" + queryParams.get(1) + "\", \"" + queryParams.get(2) + "\");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SET_VERIFICATION_REPORT_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.SET_VERIFICATION_REPORT_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to set closing initiator approval
	 * @param msg
	 * @return
	 */
	public Message initiatorApproveClose(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL initiatorApproveClose(" + queryParams.get(0) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.INITIATOR_APPROVE_CLOSE_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.INITIATOR_APPROVE_CLOSE_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to set closing inspector approval
	 * @param msg
	 * @return
	 */
	public Message inspectorApproveClose(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL inspectorApproveClose(" + queryParams.get(0) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.INSPECTOR_APPROVE_CLOSE_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.INSPECTOR_APPROVE_CLOSE_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to suspend a ticket
	 * @param msg
	 * @return
	 */
	public Message suspendTicket(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL suspendTicket(" + queryParams.get(0) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SUSPEND_TICKET_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.SUSPEND_TICKET_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}	
	
	/**
	 * Sending a request to the DB to resume a ticket
	 * @param msg
	 * @return
	 */
	public Message resumeTicket(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL resumeTicket(" + queryParams.get(0) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.RESUME_TICKET_REPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.RESUME_TICKET_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to Insert time extension request
	 * @param msg
	 * @return
	 */
	public Message setTimeExtensionRequest(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id
		// object[1] = requester_id 
		// object[2] = request date
		// object[3] = request description
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL setTimeExtensionRequest(" + queryParams.get(0) + ", " + queryParams.get(1) + ", \"" + queryParams.get(2) + "\", \"" + queryParams.get(3) + "\");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.SET_TIME_EXTENSION_REQUEST_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.SET_TIME_EXTENSION_REQUEST_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to approve time extension request
	 * @param msg
	 * @return
	 */
	public Message approveTimeExtension(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = ticket_id
		// object[1] = decision 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL approveTimeExtension(" + queryParams.get(0) + ", \"" + queryParams.get(1) + "\");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.APPROVE_TIME_EXTENSION_REQUEST_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.APPROVE_TIME_EXTENSION_REQUEST_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;		
	}
	
	/**
	 * Send a request to the DB to list all Waiting Time Extensions Requests for Inspector
	 * @param msg
	 * @return
	 */
	public Message listTimeExtensionRequests(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = inspector_id OR null 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL listTimeExtensionRequests(" + queryParams.get(0) + ");");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			ArrayList<TimeExtension> extensions = new ArrayList<TimeExtension>();
			while(rs.next()) {
				TimeExtension te = new TimeExtension(rs.getLong("ticket_id"), rs.getLong("requester_id"), rs.getString("requester_f_name"), rs.getString("requester_l_name"), rs.getString("status_name"), rs.getString("tte_request_date"), rs.getString("tte_desc"));
				extensions.add(te);
			}
			if(extensions.size() > 0) {
				returnedMsg = new Message(MsgType.LIST_TIME_EXTENSTIONS_REQUESTS_RESPONSE, ObjectType.TIME_EXTENSIONS_LIST, extensions);
			}
			else {
				returnedMsg = new Message(MsgType.LIST_TIME_EXTENSTIONS_REQUESTS_EMPTY, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.LIST_TIME_EXTENSTIONS_REQUESTS_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to replace committee chairman
	 * @param msg
	 * @return
	 */
	public Message replaceCommitteeChairman(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = old_user
		// object[1] = new_user 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL replaceCommitteeChairman(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.REPLACE_COMMITTEE_CHAIRMAN_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.REPLACE_COMMITTEE_CHAIRMAN_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to replace committee member
	 * @param msg
	 * @return
	 */
	public Message replaceCommitteeMember(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = old_user
		// object[1] = new_user 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL replaceCommitteeMember(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.REPLACE_COMMITTEE_MEMBER_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.REPLACE_COMMITTEE_MEMBER_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending a request to the DB to replace inspector to ticket
	 * @param msg
	 * @return
	 */
	public Message replaceInspectorToTicket(Message msg){
		Message returnedMsg;
		@SuppressWarnings("unchecked")
		ArrayList<String> queryParams = (ArrayList<String>) msg.getObject();
		
		// object[0] = inspector_id
		// object[1] = ticket_id 
		
		Connection conn = dbc.openConn();
		Query qry = new Query(false, "CALL replaceInspectorToTicket(" + queryParams.get(0) + ", " + queryParams.get(1) + ");");
		dbc.runQuery(qry, conn);
		returnedMsg = new Message(MsgType.REPLACE_INSPECTOR_TO_TICKET_RESPONSE, null, null);
		
		//returnedMsg = new Message(MsgType.REPLACE_INSPECTOR_TO_TICKET_ERR, null, null);
		dbc.closeConn(conn);
		return returnedMsg;
	}
	
	/**
	 * Sending request to the DB to list all Committee members and chairman
	 * @return
	 */
	public Message listAllCommitteeMembers(){
		Message returnedMsg;
		
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL listAllCommitteeMembers();");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			ArrayList<User> users = new ArrayList<User>();
			while(rs.next()) {
				User u = new User(rs.getLong("u_id"), rs.getString("u_f_name"), rs.getString("u_l_name"), rs.getString("u_email"), rs.getString("u_phone"));
				users.add(u);
			}
			if(users.size() > 0) {
				returnedMsg = new Message(MsgType.LIST_ALL_COMMITTEE_MEMBERS_RESPONSE, ObjectType.USER_ARRAY_LIST, users);
			}
			else {
				returnedMsg = new Message(MsgType.LIST_ALL_COMMITTEE_MEMBERS_EMPTY, null, null);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
			returnedMsg = new Message(MsgType.LIST_ALL_COMMITTEE_MEMBERS_ERR, null, null);
		}
		dbc.closeConn(conn);
		return returnedMsg;
	}

	/**
	 * Generate daily list of all reminders to users
	 */
	public void generateReminders(){
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL listReminders();");
		ResultSet rs = dbc.runQuery(qry, conn);
		
		try {
			while(rs.next()) {
				// System.out.println("found - r");
				Email eml = new Email(rs.getString("user_email"), rs.getString("msg_subject"), rs.getString("msg_body"));
				this.emails_list.add(eml);
				Email sms = new Email("icm.grp14@gmail.com", rs.getString("user_phone"), rs.getString("msg_body"));
				this.emails_list.add(sms);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
		}
		dbc.closeConn(conn);
	}
	
	/**
	 * Generate daily list of all today's exceptions in all tickets
	 */
	public void generateExcptions(){
		Connection conn = dbc.openConn();
		Query qry = new Query(true, "CALL listCurrentExceptions();");
		ResultSet rs =dbc.runQuery(qry, conn);
		
		try {
			while(rs.next()) {
				// System.out.println("found - e");
				Email eml = new Email(rs.getString("user_email"), rs.getString("msg_subject"), rs.getString("msg_body"));
				this.emails_list.add(eml);
			}
			rs.close();
		} catch (SQLException e) { // Connection Error
			e.printStackTrace();
		}
		dbc.closeConn(conn);
	}
	
	/**
	 * @return the emails_list
	 */
	public Queue<Email> getEmails_list() {
		return emails_list;
	}
}